<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic PUBLIC "-//OASIS//DTD DITA Topic//EN" "topic.dtd">
<topic id="topic_gzw_2wz_13b">
  <title>diskquota</title>
  <body>
    <p>The diskquota module allows Greenplum Database administrators to limit the amount of
      disk space used by schemas, roles, or tablespaces in a database.</p>
    <p>This topic includes the following sections:</p>
    <ul>
      <li><xref href="#topic_ofb_gb1_b3b" type="topic" format="dita"/></li>
      <li><xref href="#intro" type="topic" format="dita"/></li>
      <li><xref href="#topic_ndp_4wy_c3b" type="topic" format="dita"/></li>
      <li><xref href="#functions" type="topic" format="dita"/></li>
      <li><xref href="#config" type="topic" format="dita"/></li>
      <li><xref href="#using" type="topic" format="dita"/></li>
      <li><xref href="#limits" type="topic" format="dita"/></li>
      <li><xref href="#topic_sfb_gb1_b3b" type="topic" format="dita"/></li>
      <li><xref href="#upgrade" type="topic" format="dita"/></li>
      <li><xref href="#topic_v2z_jrv_b3b" type="topic" format="dita"/></li>
    </ul>
  </body>
  <topic id="topic_ofb_gb1_b3b">
    <title>Installing and Registering the Module (First Use)</title>
    <body>
      <p>The <codeph>diskquota</codeph> module is installed when you install Greenplum Database.</p>
      <p>Before you can use the module, you must perform these steps:</p>
      <ol id="ol_pfb_gb1_b3b">
        <li>Create the <codeph>diskquota</codeph> database. The <codeph>diskquota</codeph> module
          uses this database to store the list of databases where the module is enabled.
          <codeblock>$ createdb diskquota;</codeblock></li>
        <li> Add the <codeph>diskquota</codeph> shared library to the Greenplum Database
            <codeph>shared_preload_libraries</codeph> server configuration parameter and restart
          Greenplum
          Database. Be sure to retain the previous setting of the configuration parameter.
          For example:<codeblock>$ gpconfig -s shared_preload_libraries
Values on all segments are consistent
GUC              : shared_preload_libraries
Master      value: auto_explain
Segment     value: auto_explain
$ gpconfig -c shared_preload_libraries -v 'auto_explain,diskquota-2.0'
$ gpstop -ar</codeblock></li>
        <li> Register the <codeph>diskquota</codeph> extension in each database in which you want
          to enforce disk usage quotas. You can register <codeph>diskquota</codeph> in up to ten
          databases. <codeblock>$ psql -d testdb -c "CREATE EXTENSION diskquota"</codeblock></li>
        <li>If you register the <codeph>diskquota</codeph> extension in a database that already
          contains data, you must initialize the <codeph>diskquota</codeph> table size data by
          running the <codeph>diskquota.init_table_size_table()</codeph> UDF in the database. In a
          database with many files, this can take some time. The <codeph>diskquota</codeph> module
          cannot be used until the initialization is
          complete.<codeblock>=# SELECT diskquota.init_table_size_table();</codeblock>
          <note>You must run the <codeph>diskquota.init_table_size_table()</codeph> UDF
            for <codeph>diskquota</codeph> to work.</note>
        </li>
      </ol>
    </body>
  </topic>
  <topic id="intro">
    <title>About the diskquota Module</title>
    <body>
      <p>The disk usage for a table includes the table data, indexes, toast tables, and free space
        map. For append-optimized tables, the calculation includes the visibility map and index, and
        the block directory table.</p>
      <p>The <codeph>diskquota</codeph> module allows a Greenplum Database administrator to limit the
        amount of disk space used by tables in schemas or owned by roles in up to 10 databases.
        The administrator can also
        use the module to limit the amount of disk space used by schemas and roles on a
        per-tablespace basis, as well as to limit the disk space used per 
        Greenplum Database segment for a tablespace.</p>
      <note>A role-based disk quota cannot be set for the Greenplum Database system owner
        (the user that creates the Greenplum cluster).</note>
      <p>You can set the following quotas with the <codeph>diskquota</codeph> module:</p>
      <ul>
        <li>A <i>schema disk quota</i> sets a limit on the disk space that can used by all tables in
          a database that reside in a specific schema. The disk usage of a schema is defined as
          the total of disk usage on all segments for all tables in the schema.</li>
        <li>A <i>role disk quota</i> sets a limit on the disk space that can be used used by all
          tables in a database that are owned by a specific role. The disk usage for a role is
          defined as the total of disk usage on all segments for all tables the role owns.
          Although a role is a cluster-level database object, the disk usage for
          roles is calculated separately for each database.</li>
        <li>A <i>schema tablespace disk quota</i> sets a limit on the disk space that can used by
          all tables in a database that reside in a specific schema and tablespace.</li>
        <li>A <i>role tablespace disk quota</i> sets a limit on the disk space that can used by all
          tables in a database that are owned by a specific role and reside in a specific
          tablespace.</li>
        <li>A <i>per-segment tablespace disk quota</i> sets a limit on the disk space that can
          be used by a Greeplum Database segment when a tablespace quota is set for a
          schema or role.</li>
      </ul>
    </body>
  </topic>
  <topic id="topic_ndp_4wy_c3b">
    <title>Understanding How diskquota Monitors Disk Usage</title>
    <body>
      <p>A single <codeph>diskquota</codeph> launcher process runs on the active Greenplum
        Database master node. The diskquota launcher process creates and launches a
        diskquota worker process on the master for each diskquota-enabled database. A worker
        process is responsible for monitoring the disk usage of tablespaces, schemas, and
        roles in the target database, and communicates with the Greenplum segments to obtain
        the sizes of active tables. The worker process also performs quota enforcement,
        placing tablespaces, schemas, or roles on a denylist when they reach their
        quota.</p>
      <p>When a query plan for a data-adding query is generated, and the tablespace, schema,
        or role into which data would be loaded is on the denylist,
        <codeph>diskquota</codeph> cancels the query
        before it starts executing, and reports an error message that the quota has been
        exceeded.</p>
      <p>A query that does not add data, such as a simple <codeph>SELECT</codeph> query, is
        always allowed to run, even when the tablespace, role, or schema is on the denylist.</p>
      <p>Diskquota can enforce both <i>soft limits</i> and <i>hard limits</i> for disk usage:
        <ul>
          <li>By default, <codeph>diskquota</codeph> always enforces soft limits.
            <codeph>diskquota</codeph> checks quotas before a query runs. If quotas are
            not exceeded when a query is initiated, <codeph>diskquota</codeph> allows the
            query to run, even if it were to eventually cause a quota to be exceeded.</li>
          <li>When hard limit enforcement of disk usage is enabled, <codeph>diskquota</codeph>
            also monitors disk usage during query execution. If a query exceeds a disk quota
            during execution, <codeph>diskquota</codeph> terminates the query.
            <p>Administrators can enable enforcement of a disk usage hard limit by setting the
              <codeph>diskquota.hard_limit</codeph> server configuration parameter as
              described in <xref href="#config/hardlimit" type="topic" format="dita"></xref>.</p></li>
        </ul></p>
      <p>There is some delay after a quota has been reached before the schema or role is added to
        the denylist. Other queries could add more data during the delay. The delay occurs because
          <codeph>diskquota</codeph> processes that calculate the disk space used by each table
        run periodically with a pause between executions (two seconds by default). The delay
        also occurs when disk usage falls beneath a quota, due to operations such as
          <codeph>DROP</codeph>, <codeph>TRUNCATE</codeph>, or <codeph>VACUUM FULL</codeph> that
        remove data. Administrators can change the amount of time between disk space checks
        by setting the <codeph>diskquota.naptime</codeph> server configuration parameter
        as described in <xref href="#config/naptime" type="topic" format="dita"></xref>.</p>
      <p>If a query is unable to run because the tablespace, schema, or role has been denylisted, an
        administrator can increase the exceeded quota to allow the query to run. The module
        provides views that you can use to find the tablespaces, schemas, or roles that have exceeded
        their limits.</p>
    </body>
  </topic>
  <topic id="functions">
    <title>About the diskquota Functions and Views</title>
    <body>
      <p>The <codeph>diskquota</codeph> module provides user-defined functions (UDFs) and views
        that you can use to manage and monitor disk space usage in your Greenplum Database
        deployment.</p>
      <p>The functions and views provided by the <codeph>diskquota</codeph> module are 
        available in the Greenplum Database schema named <codeph>diskquota</codeph>.
        <note>You may be required to prepend the schema name (<codeph>diskquota.</codeph>)
        to any UDF or view that you access.</note></p>
      <p>User-defined functions provided by the module include:</p>
        <table id="function_table">
        <tgroup cols="2">
          <colspec colname="col1" colnum="1" colwidth="86*"/>
          <colspec colname="col2" colnum="2" colwidth="77*"/>
          <thead>
            <row>
              <entry colname="col1">Function Signature</entry>
              <entry colname="col2">Description</entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry colname="col1">void init_table_size_table()</entry>
              <entry colname="col2">Sizes the existing tables in the current database.</entry>
            </row>
            <row>
              <entry colname="col1">void set_role_quota( role_name text, quota text )</entry>
              <entry colname="col2">Sets a disk quota for a specific role in the current database.
                <note>A role-based disk quota cannot be set for the Greenplum Database
                  system owner.</note></entry>
            </row>
            <row>
              <entry colname="col1">void set_role_tablespace_quota( role_name text, tablespace_name text, quota text )</entry>
              <entry colname="col2">Sets a disk quota for a specific role and tablespace
                combination in the current database.
                <note>A role-based disk quota cannot be set for the Greenplum Database
                  system owner.</note></entry>
            </row>
            <row>
              <entry colname="col1">void set_schema_quota( schema_name text, quota text )</entry>
              <entry colname="col2">Sets a disk quota for a specific schema in the current database.</entry>
            </row>
            <row>
              <entry colname="col1">void set_schema_tablespace_quota( schema_name text, tablespace_name text, quota text )</entry>
              <entry colname="col2">Sets a disk quota for a specific schema and tablespace                  combination in the current database.</entry>
            </row>
            <row>
              <entry colname="col1">void set_per_segment_quota( tablespace_name text, ratio float4 )</entry>
              <entry colname="col2">Sets a per-segment disk quota for a tablespace in the current database.</entry>
            </row>
            <row>
              <entry colname="col1">void pause()</entry>
              <entry colname="col2">Instructs the module to continue to count disk usage
                for the current database, but pause and cease emitting an error when the
                limit is exceeded.</entry>
            </row>
            <row>
              <entry colname="col1">void resume()</entry>
              <entry colname="col2">Instructs the module to resume emitting an error when the
                disk usage limit is exceeded in the current database.</entry>
            </row>
            <row>
              <entry colname="col1">status() RETURNS table</entry>
              <entry colname="col2">Displays the <codeph>diskquota</codeph> binary and
                schema versions and the status of soft and hard limit disk usage enforcement
                in the current database.</entry>
            </row>
          </tbody>
        </tgroup>
        </table>

      <p>Views available in the <codeph>diskquota</codeph> module include:</p>
        <table id="view_table">
        <tgroup cols="2">
          <colspec colname="col1" colnum="1" colwidth="86*"/>
          <colspec colname="col2" colnum="2" colwidth="77*"/>
          <thead>
            <row>
              <entry colname="col1">View Name</entry>
              <entry colname="col2">Description</entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry colname="col1">show_fast_database_size_view</entry>
              <entry colname="col2">Displays the disk space usage in the current database.</entry>
            </row>
            <row>
              <entry colname="col1">show_fast_role_quota_view</entry>
              <entry colname="col2">Lists active quotas for roles in the current database.</entry>
            </row>
            <row>
              <entry colname="col1">show_fast_role_tablespace_quota_view </entry>
              <entry colname="col2">List active quotas for roles per tablespace in the
                current database.</entry>
            </row>
            <row>
              <entry colname="col1">show_fast_schema_quota_view</entry>
              <entry colname="col2">Lists active quotas for schemas in the current database.</entry>
            </row>
            <row>
              <entry colname="col1">show_fast_schema_tablespace_quota_view </entry>
              <entry colname="col2">Lists active quotas for schemas per tablespace in the
                current database.</entry>
            </row>
            <row>
              <entry colname="col1">show_segment_ratio_quota_view</entry>
              <entry colname="col2">Displays the per-segment disk quota ratio for any
                per-segment tablespace quotas set in the current database.</entry>
            </row>
          </tbody>
        </tgroup>
        </table>
    </body>
  </topic>
  <topic id="config">
    <title>Configuring the diskquota Module</title>
    <body>
      <p><codeph>diskquota</codeph> exposes server configuration parameters that allow you
        to control certain module functionality:</p>
      <ul>
        <li><xref href="#config/naptime" format="dita">diskquota.naptime</xref> - Controls
          how frequently (in seconds) that <codeph>diskquota</codeph> recalculates the table
          sizes.</li>
        <li><xref href="#config/shmem" format="dita">diskquota.max_active_tables</xref> -
          Identifies the maximum number of relations
          (including tables, indexes, etc.) that the <codeph>diskquota</codeph> module can
          monitor at the same time.</li>
        <li><xref href="#config/hardlimit" format="dita">diskquota.hard_limit</xref>
          - Enables or disables the hard limit enforcement of disk usage.</li>
      </ul>
      <p>You use the <codeph>gpconfig</codeph> command to set these parameters in the same way
        that you would set any Greenplum Database server configuration parameter.</p>
      <section id="naptime">
        <title>Setting the Delay Between Disk Usage Updates</title>
        <p>The <codeph>diskquota.naptime</codeph> server configuration parameter specifies
          how frequently (in seconds) <codeph>diskquota</codeph> recalculates the table
          sizes. The smaller the
          <codeph>naptime</codeph> value, the less delay in detecting changes in disk usage.
          This example sets the <codeph>naptime</codeph> to ten seconds and restarts
          Greenplum Database:</p>
        <codeblock>$ gpconfig -c diskquota.naptime -v 10
$ gpstop -ar</codeblock>
      </section>
      <section id="shmem">
        <title>About Shared Memory and the Maximum Number of Relations</title>
        <p>The <codeph>diskquota</codeph> module uses shared memory to save the denylist and to save
          the active table list.</p>
        <p>The denylist shared memory can hold up to one million database objects that exceed the quota
          limit. If the denylist shared memory fills, data may be loaded into some schemas or roles
          after they have reached their quota limit. </p>
        <p>Active table shared memory holds up to one million of active tables by default. Active tables
          are tables that may have changed sizes since <codeph>diskquota</codeph> last recalculated
          the table sizes. <codeph>diskquota</codeph> hook functions are called when the storage
          manager on each Greenplum Database segment creates, extends, or truncates a table file.
          The hook functions store the identity of the file in shared memory so that its file size
          can be recalculated the next time the table size data is refreshed. </p>
        <p>The <codeph>diskquota.max_active_tables</codeph> server configuration parameter
          identifies the maximum number of relations (including tables, indexes, etc.) that
          the <codeph>diskquota</codeph> module can monitor at the same time. The default
          value is <codeph>1 * 1024 * 1024</codeph>. This value should be sufficient for most
          Greenplum Database installations. Should you change the value of this
          configuration parameter, you must restart the Greenplum Database server.</p>
      </section>
      <section id="hardlimit">
        <title>Enabling/Disabling Hard Limit Disk Usage Enforcement</title>
        <p>When you enable enforcement of a hard limit of disk usage, <codeph>diskquota</codeph>
          checks the quota during query execution. If at any point a currently running query
          exceeds a quota limit, <codeph>diskquota</codeph> terminates the query.</p>
        <p>By default, hard limit disk usage enforcement is disabled for all databases. To
          enable hard limit enforcement for all databases, set the
          <codeph>diskquota.hard_limit</codeph> server configuration parameter to
          <codeph>'on'</codeph>, and then reload the Greenplum Database configuration:</p>
        <codeblock>$ gpconfig -c diskquota.hard_limit -v 'on'
$ gpstop -u</codeblock>
        <p>Run the following query to view the hard limit enforcement setting:</p>
        <codeblock>SELECT * from diskquota.status();</codeblock>
      </section>
    </body>
  </topic>
  <topic id="using">
    <title>Using the diskquota Module</title>
    <body>
      <p>You can perform the following tasks with the <codeph>diskquota</codeph> module:</p>
      <ul>
        <li><xref href="#using/status" format="dita">View the diskquota Status</xref></li>
        <li><xref href="#using/pause_resume" format="dita">Pause and Resume Disk Quota
          Exceeded Notifications</xref></li>
        <li><xref href="#using/schema_or_role_quota" format="dita">Set a Schema or Role Disk
          Quota</xref></li>
        <li><xref href="#using/tablespace_quota" format="dita">Set a Tablespace Disk Quota
          for a Schema or Role</xref></li>
        <li><xref href="#using/per_seg_tblsp_quota" format="dita">Set a Per-Segment
          Tablespace Disk Quota</xref></li>
        <li><xref href="#using/quotas_usage" format="dita">Display Disk Quotas and Disk
          Usage</xref></li>
        <li><xref href="#using/temp_disable" format="dita">Temporarily Disable
          Disk Quota Monitoring</xref></li>
      </ul>
      <section id="status">
        <title>Viewing the diskquota Status</title>
        <p>To view the <codeph>diskquota</codeph> module and schema version numbers, and the
          state of soft and hard limit enforcement in the current database, invoke the
          <codeph>status()</codeph> command:</p>
        <codeblock>SELECT diskquota.status();
          name          | status 
------------------------+--------- 
 soft limits            | on 
 hard limits            | on 
 current binary version | 2.0.0 
 current schema version | 2.0 </codeblock>
      </section>
      <section id="pause_resume">
        <title>Pausing and Resuming Disk Quota Exceeded Notifications</title>
        <p>If you do not care to be notified of disk quota exceeded events for a period of
          time, you can pause and resume error notification in the current database as 
          shown below:</p>
        <codeblock>SELECT diskquota.pause();
-- perform table operations where you do not care to be notified
-- when a disk quota exceeded
SELECT diskquota.resume(); </codeblock>
      </section>
      <section id="schema_or_role_quota">
        <title>Setting a Schema or Role Disk Quota</title>
        <p>Use the <codeph>diskquota.set_schema_quota()</codeph> and
          <codeph>diskquota.set_role_quota()</codeph> user-defined functions in a database to set,
          update, or delete disk quota limits for schemas and roles in the database. The functions
          take two arguments: the schema or role name, and the quota to set. You can specify the
          quota in units of MB, GB, TB, or PB; for example, <codeph>'2TB'</codeph>. </p>
        <p>The following example sets a 250GB quota for the <codeph>acct</codeph> schema:</p>
        <codeblock>SELECT diskquota.set_schema_quota('acct', '250GB');</codeblock>
        <p>This example sets a 500MB disk quota for the <codeph>nickd</codeph> role:</p>
        <codeblock>SELECT diskquota.set_role_quota('nickd', '500MB');</codeblock>
        <p>To change a quota, invoke the <codeph>diskquota.set_schema_quota()</codeph> or
            <codeph>diskquota.set_role_quota()</codeph> function again with the new quota value.</p>
        <p>To remove a schema or role quota, set the quota value to <codeph>'-1'</codeph>
          and invoke the function.</p>
      </section>
      <section id="tablespace_quota">
        <title>Setting a Tablespace Disk Quota</title>
        <p>Use the <codeph>diskquota.set_schema_tablespace_quota()</codeph> and
          <codeph>diskquota.set_role_tablespace_quota()</codeph> user-defined functions in a
          database to set, update, or delete per-tablespace disk quota limits for schemas
          and roles in the current database. The functions take three arguments: the 
          schema or role name, the tablespace name, and the quota to set. You can specify
          the quota in units of MB, GB, TB, or PB; for example, <codeph>'2TB'</codeph>. </p>
        <p>The following example sets a 50GB disk quota for the tablespace named
          <codeph>tspaced1</codeph> and the <codeph>acct</codeph> schema:</p>
        <codeblock>SELECT diskquota.set_schema_tablespace_quota('acct', 'tspaced1', '250GB');</codeblock>
        <p>This example sets a 500MB disk quota for the <codeph>tspaced2</codeph> tablespace
          and the <codeph>nickd</codeph> role:</p>
        <codeblock>SELECT diskquota.set_role_tablespace_quota('nickd', 'tspaced2', '500MB');</codeblock>
        <p>To change a quota, invoke the <codeph>diskquota.set_schema_tablespace_quota()</codeph>
          or <codeph>diskquota.set_role_tablespace_quota()</codeph> function again with the
          new quota value.</p>
        <p>To remove a schema or role tablespace quota, set the quota value to <codeph>'-1'</codeph>
          and invoke the function.</p>
      </section>
      <section id="per_seg_tblsp_quota">
        <title>Setting a Per-Segment Tablespace Disk Quota</title>
        <p>When an administrator sets a tablespace quota for a schema or a role, they may
          also choose to define a per-segment disk quota for the tablespace. Setting a
          per-segment quota limits the amount of disk space on a single Greenplum Database
          segment that a single tablespace may consume, and
          may help prevent a segment's disk from filling due to data skew.</p>
        <p>You can use the <codeph>diskquota.set_per_segment_quota()</codeph> function to
          set, update, or delete a per-segment tablespace disk quota limit. The function
          takes two arguments: the tablespace name and a ratio. The ratio identifies
          how much more of the disk quota a single segment can use than the average segment
          quota. A ratio that you specify must be greater than zero.</p>
        <p>You can calculate the average segment quota as follows:</p>
        <codeblock>avg_seg_quota = tablespace_quota / number_of_segments</codeblock>
        <p>For example, if your Greenplum Database cluster has 8 segments and you set the
          following schema tablespace quota:</p>
        <codeblock>SELECT diskquota.set_schema_tablespace_quota( 'accts', 'tspaced1', '800GB' );</codeblock>
        <p>The average segment quota for the <codeph>tspaced1</codeph> tablespace is
          <codeph>800GB / 8 = 100GB</codeph>.</p>
        <p>If you set the following per-segment tablespace quota:</p>
        <codeblock>SELECT diskquota.set_per_segment_quota( 'tspaced1', '2.0' );</codeblock>
        <p>You can calculate the the maximum allowed disk usage per segment allowed as follows:</p>
        <codeblock>max_disk_usage_per_seg = average_segment_quota * ratio</codeblock>
        <p>In this example, the maximum disk usage allowed per segment is
          <codeph>100GB * 2.0 = 200GB</codeph>.</p>
        <p><codeph>diskquota</codeph> will allow a query to run if the disk usage on all
          segments for all tables that are in tablespace <codeph>tblspc1</codeph> and that
          are goverend by a role or schema quota does not exceed <codeph>200GB</codeph>.</p>
        <p>You can change the per-segment tablespace quota by invoking the
          <codeph>diskquota.set_per_segment_quota()</codeph>
          function again with the new quota value.</p>
        <p>To remove a per-segment tablespace quota, set the quota value to <codeph>'-1'</codeph>
          and invoke the function.</p>
        <p>To view the per-segment quota ratio set for a tablespace, display the
          <codeph>show_segment_ratio_quota_view</codeph> view. For example:</p>
        <codeblock>SELECT tablespace_name, per_seg_quota_ratio
  FROM diskquota.show_segment_ratio_quota_view WHERE tablespace_name in ('tspaced1');
  tablespace_name  | per_seg_quota_ratio
-------------------+---------------------
 tspaced1          |                   2
(1 rows)
</codeblock>
      </section>
      <section id="quotas_usage">
        <title>Displaying Disk Quotas and Disk Usage</title>
        <p>The <codeph>diskquota</codeph>
          module provides four views to display active quotas and the current computed disk space
          used.</p>The <codeph>diskquota.show_fast_schema_quota_view</codeph> view lists active
        quotas for schemas in the current database. The <codeph>nspsize_in_bytes</codeph> column
        contains the calculated size for all tables that belong to the schema.
          <codeblock>SELECT * FROM diskquota.show_fast_schema_quota_view;
 schema_name | schema_oid | quota_in_mb | nspsize_in_bytes
-------------+------------+-------------+------------------
 acct        |      16561 |      256000 |           131072
 analytics   |      16519 |  1073741824 |        144670720
 eng         |      16560 |     5242880 |        117833728
 public      |       2200 |         250 |          3014656
(4 rows)</codeblock>
        <p>The
          <codeph>diskquota.show_fast_role_quota_view</codeph> view lists the active quotas for
          roles in the current database. The <codeph>rolsize_in_bytes</codeph> column contains the
          calculated size for all tables that are owned by the
        role.</p><codeblock>SELECT * FROM diskquota.show_fast_role_quota_view;
 role_name | role_oid | quota_in_mb | rolsize_in_bytes
-----------+----------+-------------+------------------
 mdach     |    16558 |         500 |           131072
 adam      |    16557 |         300 |        117833728
 nickd     |    16577 |         500 |        144670720
(3 rows)</codeblock>
        <p>You can view the per-tablespace disk quotas for schemas and roles
          with the <codeph>diskquota.show_fast_schema_tablespace_quota_view</codeph> and
          <codeph>diskquota.show_fast_role_tablespace_quota_view</codeph> views. For example:</p>
        <codeblock>SELECT schema_name, tablespace_name, quota_in_mb, nspsize_tablespace_in_bytes
   FROM diskquota.show_fast_schema_tablespace_quota_view
   WHERE schema_name = 'acct' and tablespace_name ='tblspc1';
 schema_name | tablespace_name | quota_in_mb | nspsize_tablespace_in_bytes
-------------+-----------------+-------------+-----------------------------
 acct        | tspaced1        |      250000 |                      131072
(1 row)
</codeblock>
      </section>
      <section id="temp_disable">
        <title>About Temporarily Disabling diskquota</title>
        <p>You can temporarily disable the <codeph>diskquota</codeph> module by removing
          the shared library from <codeph>shared_preload_libraries</codeph>. For example::</p>
        <codeblock>$ gpconfig -s shared_preload_libraries
Values on all segments are consistent
GUC              : shared_preload_libraries
Master      value: auto_explain,diskquota-2.0
Segment     value: auto_explain,diskquota-2.0
$ gpconfig -c shared_preload_libraries -v 'auto_explain'
$ gpstop -ar</codeblock>
        <note>When you disable the <codeph>diskquota</codeph> module in this manner,
          disk quota monitoring ceases. To re-initiate disk quota monitoring in this
          scenario, you must:
          <ol>
            <li>Re-add the library to <codeph>shared_preload_libraries</codeph>.</li>
            <li>Restart Greenplum Database.</li>
            <li>Re-size the existing tables in the database by running:
              <codeph>SELECT diskquota.init_table_size_table();</codeph></li>
            <li>Restart Greenplum Database again.</li>
          </ol>
        </note>
      </section>
    </body>
  </topic>
  <topic id="limits">
    <title>Known Issues and Limitations</title>
    <body>
      <p>The <codeph>diskquota</codeph> module has the following limitations and known
        issues:</p>
      <ul>
        <li><codeph>diskquota</codeph> does not automatically work on a segment when the
          segment is replaced by a mirror. You must manually restart Greenplum Database in
          this circumstance.</li>
        <li><codeph>diskquota</codeph> cannot enforce a hard limit on
          <codeph>ALTER TABLE ADD COLUMN DEFAULT</codeph> operations.</li>
        <li>If Greenplum Database restarts due to a crash, you must run
          <codeph>SELECT diskquota.init_table_size_table();</codeph> to ensure that
          the disk usage statistics are accurate.</li>
        <li>To avoid the chance of deadlock, you must first pause the
          <codeph>diskquota</codeph> extension before you drop the extension in any database:
          <codeblock>SELECT diskquota.pause();
DROP EXTENSION diskquota;</codeblock></li>
        <li><codeph>diskquota</codeph> may record an incorrect table size after
          <codeph>ALTER TABLESPACE</codeph>, <codeph>TRUNCATE</codeph>, or other operations
          that modify the <codeph>relfilenode</codeph> of the table. <p>Cause:
          <codeph>diskquota</codeph> does not acquire any locks on a relation when
          computing the table size. If another session is updating the table's tablespace
          while <codeph>diskquota</codeph> is calculating the table size, an error can occur.</p>
          <p>In most cases, you can ignore the
          difference; <codeph>diskquota</codeph> will update the size when new data is next
          ingested. To immediately ensure that the disk usage statistics are accurate, invoke:</p>
          <codeblock>SELECT diskquota.init_table_size_table();</codeblock>
          <p>And then restart Greenplum Database.</p></li>
        <li>In rare cases, a <codeph>VACUUM FULL</codeph> operation may exceed
          a quota limit. To remedy the situation, pause <codeph>diskquota</codeph> before
          the operation and then resume <codeph>diskquota</codeph> after:
          <codeblock>SELECT diskquota.pause();
-- perform the VACUUM FULL
SELECT diskquota.resume();</codeblock>
          <p>If you do not want to pause/resume <codeph>diskquota</codeph>, you may choose
            to temporarily set a higher quota for the operation and then set it back when the
            <codeph>VACUUM FULL</codeph> completes. Consider the following:
            <ul>
              <li>If you <codeph>VACUUM FULL</codeph> only a single table, set the quota to
                be no smaller than the size of that table.</li>
              <li>If you <codeph>VACUUM FULL</codeph> all tables, set the quota to be no
                smaller than the size of the largest table in the database.</li>
            </ul></p></li>
        <li>The size of uncommitted tables are not counted in quota views. Even though the
          <codeph>diskquota.show_fast_role_quota_view</codeph> view may display a smaller
          used quota than the quota limit, a new query may trigger a quota exceeded
          condition in the following circumstance:
          <ul>
            <li>Hard limit enforcement of disk usage is disabled.</li>
            <li>A long-running query in a session has consumed the full disk quota.</li>
          </ul>
          <codeph>diskquota</codeph> does update the denylist in this scenario, but
          the <codeph>diskquota.show_fast_role_quota_view</codeph> may not represent the
          actual used quota because the long-running query is not yet committed.
          If you execute a new query while the original is still running, the new query
          will trigger a quota exceeded error.</li>
      </ul>
    </body>
  </topic>
  <topic id="topic_sfb_gb1_b3b">
    <title>Notes</title>
    <body>
      <p>The <codeph>diskquota</codeph> module can detect a newly created table inside of an
        uncommitted transaction. The size of the new table is included in the disk usage
        calculated for the corresponding schema or role. Hard limit enforcement of disk
        usage must enabled for a quota-exceeding operation to trigger a
        <codeph>quota exceeded</codeph> error in this scenario.</p>
      <p>Deleting rows or running <codeph>VACUUM</codeph> on a table does not release disk space, so
        these operations cannot alone remove a schema or role from the <codeph>diskquota</codeph>
        denylist. The disk space used by a table can be reduced by running <codeph>VACUUM
          FULL</codeph> or <codeph>TRUNCATE TABLE</codeph>.</p>
      <p>The <codeph>diskquota</codeph> module supports high availability features provided by the
        background worker framework. The <codeph>diskquota</codeph> launcher process only runs on
        the active master node. The postmaster on the standby master does not start the
          <codeph>diskquota</codeph> launcher process when it is in standby mode. When the master is
        down and the administrator runs the <codeph><xref
            href="../../utility_guide/ref/gpactivatestandby.xml#topic1"/></codeph>
        command, the standby master changes its role to master and the <codeph>diskquota</codeph>
        launcher process is forked automatically. Using the <codeph>diskquota</codeph>-enabled
        database list in the <codeph>diskquota</codeph> database, the <codeph>diskquota</codeph>
        launcher creates the <codeph>diskquota</codeph> worker processes that manage disk quotas for
        each database.</p>
    </body>
  </topic>
  <topic id="upgrade">
    <title>Upgrading the Module to Version 2.0</title>
    <body>
      <p>The <codeph>diskquota</codeph> 2.0 module is installed when you install or upgrade
        Greenplum Database. Version 1.x of the module will continue to work after you
        upgrade Greenplum.</p>
      <p>If you are using version 1.x of the module and you want to upgrade to
        <codeph>diskquota</codeph> version 2.0, you must perform the following procedure:
        <note><codeph>diskquota</codeph> will be paused during the upgrade procedure and
          will be automatically resumed when the upgrade completes.</note>
      </p>
      <ol>
        <li>Replace the <codeph>diskquota</codeph> shared library in the Greenplum Database
          <codeph>shared_preload_libraries</codeph> server configuration parameter setting
          and restart Greenplum Database. Be sure to retain the other libraries. For example:
          <codeblock>$ gpconfig -s shared_preload_libraries
Values on all segments are consistent
GUC              : shared_preload_libraries
Master      value: auto_explain,diskquota
Segment     value: auto_explain,diskquota
$ gpconfig -c shared_preload_libraries -v 'auto_explain,diskquota-2.0'
$ gpstop -ar</codeblock></li>
        <li>Update the <codeph>diskquota</codeph> extension in every database in which you
          registered the module:
          <codeblock>$ psql -d testdb -c "ALTER EXTENSION diskquota UPDATE TO '2.0'";</codeblock></li>
        <li>Re-initialize <codeph>diskquota</codeph> table size data:
          <codeblock>=# SELECT diskquota.init_table_size_table();</codeblock></li>
        <li>Restart Greenplum Database:
          <codeblock>$ gpstop -ar</codeblock></li>
      </ol>
      <p>After upgrade, your existing disk quota rules continue to be
        enforced, and you can define new tablespace or per-segment rules. You can also
        utilize the new pause/resume disk quota enforcement functions.</p>
    </body>
  </topic>
  <topic id="topic_v2z_jrv_b3b">
    <title>Examples</title>
    <body>
     <section>
      <title>Setting a Schema Quota</title>
      <p>This example demonstrates how to configure a schema quota and then observe
        <codeph>diskquota</codeph> soft limit behavior as data is added to the schema.
        The example assumes that the <codeph>diskquota</codeph> processes are
        configured and running.</p>
      <ol id="ol_rfb_gb1_b3b">
        <li>Create a database named <codeph>testdb</codeph> and connect to
          it.<codeblock>$ createdb testdb
$ psql -d testdb</codeblock></li>
        <li>Create the diskquota extension in the
          database.<codeblock>CREATE EXTENSION diskquota;</codeblock></li>
        <li> Create a schema named <codeph>s1</codeph>:
          <codeblock>CREATE SCHEMA s1;</codeblock></li>
        <li>Set a 1MB disk quota for the <codeph>s1</codeph>
          schema.<codeblock>SELECT diskquota.set_schema_quota('s1', '1MB');</codeblock></li>
        <li>Run the following commands to create a table in the <codeph>s1</codeph> schema and insert a
          small amount of data into it. The schema has no data yet, so it is not on the denylist.
          <codeblock>SET search_path TO s1;
CREATE TABLE a(i int);
INSERT INTO a SELECT generate_series(1,100);</codeblock></li>
        <li>Insert a large amount of data, enough to exceed the 1MB quota that was set
          for the schema. Before the <codeph>INSERT</codeph> command, the <codeph>s1</codeph> schema
          is still not on the denylist, so this command should be allowed to run with only
          soft limit disk usage enforcement in effect, even though the operation will exceed
          the limit set for the schema.
          <codeblock>INSERT INTO a SELECT generate_series(1,10000000);</codeblock></li>
        <li>Attempt to insert a small amount of data. Because the previous command
          exceeded the schema's disk quota soft limit, the schema should be denylisted and any data
          loading command should be cancelled.
          <codeblock>INSERT INTO a SELECT generate_series(1,100);
ERROR:  schema's disk space quota exceeded with name: s1</codeblock></li>
        <li>Remove the quota from the <codeph>s1</codeph> schema by setting it to
            <codeph>-1</codeph> and again inserts a small amount of data. A 5-second sleep before
          the <codeph>INSERT</codeph> command ensures that the <codeph>diskquota</codeph> table size
          data is updated before the command is
          run.<codeblock>SELECT diskquota.set_schema_quota('s1', '-1');
-- Wait for 5 seconds to ensure that the denylist is updated
SELECT pg_sleep(5);
INSERT INTO a SELECT generate_series(1,100);</codeblock></li>
      </ol>
     </section>
     <section>
      <title>Enabling Hard Limit Disk Usage Enforcement and Exceeding Quota</title>
      <p>In this example, we enable hard limit enforcement of disk usage, and re-run
        commands from the previous example.</p>
      <ol>
        <li>Enable hard limit disk usage enforcement:
          <codeblock>$ gpconfig -c diskquota.hard_limit -v 'on'
$ gpstop -u</codeblock></li>
        <li>Run the following query to view the hard limit enforcement setting:
          <codeblock>SELECT * from diskquota.status();</codeblock></li>
        <li>Re-set a 1MB disk quota for the <codeph>s1</codeph>
          schema.<codeblock>SELECT diskquota.set_schema_quota('s1', '1MB');</codeblock></li>
        <li>Insert a large amount of data, enough to exceed the 1MB quota that was set
          for the schema. Before the <codeph>INSERT</codeph> command, the <codeph>s1</codeph> schema
          is still not on the denylist, so this command should be allowed to start.
          When the operation exceeds the schema quota,
          <codeph>diskquota</codeph> will terminate the query.
          <codeblock>INSERT INTO a SELECT generate_series(1,10000000);
[hardlimit] schema's disk space quota exceeded</codeblock></li>
        <li>Remove the quota from the <codeph>s1</codeph> schema:
          <codeblock>SELECT diskquota.set_schema_quota('s1', '-1');</codeblock></li>
      </ol>
     </section>
     <section>
      <title>Setting a Per-Segment Tablespace Quota</title>
      <p>This example demonstrates how to configure tablespace and per-segment tablespace
        quotas. In addition to using the <codeph>testdb</codeph> database and the
        <codeph>s1</codeph> schema that you created in the previous example, this example
        assumes the following:</p>
      <ul>
        <li>Hard limit enforcement of disk usage is enabled (as in the previous example).</li>
        <li>The Greenplum Database cluster has 8 primary segments.</li>
        <li>A tablespace named <codeph>tbsp1</codeph> has been created in the cluster.</li>
      </ul>
      <p>Procedure:</p>
      <ol>
        <li>Set a disk quota of <codeph>1 MB</codeph> for the tablespace named
         <codeph>tbsp1</codeph> and the schema named <codeph>s1</codeph>:
         <codeblock>SELECT diskquota.set_schema_tablespace_quota('s1', 'tbsp1', '1MB');</codeblock></li>
        <li>Set a per-segment ratio of <codeph>2</codeph> for the <codeph>tbsp1</codeph> tablespace:
         <codeblock>SELECT diskquota.set_per_segment_quota('tbsp1', 2);</codeblock>
         <p>With this ratio setting, the average segment quota is <codeph>1MB / 8 = 125KB</codeph>,
           and the max per-segment disk usage for the tablespace is
           <codeph>125KB * 2 = 250KB</codeph>.</p></li>
        <li>Create a new table named <codeph>b</codeph> and insert some data:
          <codeblock>CREATE TABLE b(i int);
INSERT INTO b SELECT generate_series(1,100);</codeblock></li>
        <li>Insert a large amount of data into the table, enough to exceed the 250KB
          per-segment quota that was set for the tablespace. When the operation exceeds the
          per-segment tablespace quota, <codeph>diskquota</codeph> will terminate the query.
          <codeblock>INSERT INTO b SELECT generate_series(1,10000000);
ERROR:  tablespace: tbsp1, schema: s1 diskquota exceeded per segment quota</codeblock></li>
      </ol>
     </section>
    </body>
  </topic>
</topic>
